The present invention relates to the field of information processing by digital computers, and more particularly to a method and system, in an electronic spreadsheet, for defining and processing a cell named range which can be extended by the introduction of new elements.
Before computers, numerical analysis, particularly financial ones, were usually prepared on an accountant""s columnar pad or spreadsheet, with pencil and calculator in hand. By organizing data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of xe2x80x9celectronic spreadsheets.xe2x80x9d Better known simply as xe2x80x9cspreadsheets,xe2x80x9d these software programs provide a computerized replacement for the traditional financial modeling tools: the accountant""s columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what word processors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant""s columnar pad, thus providing a visible calculator for a user. Because this xe2x80x9cpadxe2x80x9d exists dynamically in the computer""s memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, a letter to each column, and another letter to each sheet (or page) of the spreadsheet. To reference a location at column A and row 1 of the second page (i.e., the upper-lefthand corner), for example, the user types in xe2x80x9cB:A1xe2x80x9d. In this manner, the spreadsheet defines an addressable storage location or xe2x80x9ccellxe2x80x9d at each intersection of a row with a column within a given page.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant""s pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or xe2x80x9cformulasxe2x80x9d specifying calculations to be performed on the numbers stored in spreadsheet cells. Such spreadsheet cells can also be defined and named as a range as long as they are arranged as a connex set of cells. A typical example of such a named range simply corresponds to a regular table found in an accountant""s pad. In this fashion, range names can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, S., Using Quattro Pro 2, Borland-OsbomelMcGraw-MII, 1990; and LeBlond, G. and Cobb, D., Using 1-2-3, Que corp., 1985. The disclosures of each of the foregoing are hereby incorporated by reference.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform xe2x80x9cwhat-ifxe2x80x9d scenarios. After a set of computational relationships has been entered into a worksheet, thanks to imbedded formulas for instance, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Thus, electronic spreadsheet systems were invented to solve xe2x80x9cwhat-ifxe2x80x9d problems, that is, changing an input and seeing what happens to an output.
Named ranges used to automate the computations in a spreadsheet can evolve during the xe2x80x9clifexe2x80x9d of a spreadsheet just as some entries may be added to or deleted from a classical table found in an accountant""s pad. Conventional spreadsheet tools offer some means for automatically adjusting the content of an existing named range when new elements (typically a new column, a new row or a new sheet) are added between elements already pertaining to the named range, or when some elements (typically a column, a row or a sheet) already pertaining to the named range are deleted. The relative location within the named range where new elements are added is commonly the choice of the spreadsheet user, motivated by his own criteria, such as, for instance, some sorting criteria. If the spreadsheet user decides to add to an existing named range some new elements just below the bottom one, or just above the first one, then conventional spreadsheets do not automatically update the named range because the newly introduced elements are not located between two elements pertaining to the named range. This limitation can be quite cumbersome in the frequent case where elements of a table are horizontally sorted by date, so that a new (recent) element of the named range must be commonly added by the introduction of a new row just below the last one of the named range.
An object of the present invention is to automatically adjust the limits of a named range for adding new elements (row, or column, or sheet) which are not located between two elements already pertaining to the named range, but in the immediate vicinity of an element pertaining to the named range.
The present invention relates to a method and system for processing a named range defined by a cell address range in a spreadsheet comprising a plurality of cells identified in each dimension by an address, each dimension comprising two directions. The method comprises the steps of:
defining an open named range by specifying to the named range one or a plurality of open directions; and
defining a hidden named range with the cell address range of the open named range expanded by one address in every direction specified as open.
In response to user action, the method further comprises the steps of:
selecting one or a plurality of directions;
increasing the cell address range of the hidden named range by inserting between two consecutive cell addresses, a new address in selected directions; and
updating the open named range with the cell address range of the hidden named range shortened by one address in every direction specified as open.